''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'        Goal: Dump all tests from QC to Excel               '
'        Ver:  0.0.0.1                                       '
'        By:   Michael                                       '
'        On:   May 19th, 2009                                '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


Option Explicit Off

Module __dump_tests_from_qualitycenter__

    Const xlLeft    = -4131
    Const xlRight   = -4152
    Const xlCenter  = -4108
    Const xlGeneral = 1


    Sub Main
        Dim QCConnection As New TDAPIOLELib.TDConnection

        Dim QCURL     = "http://qcurl/bin/"
        Dim QCDomain  = "domain"
        Dim QCProject = "project"
        Dim QCUser    = "usr"
        Dim QCPasswd  = "passwd"
        Dim path      = ".\Subject\abc\blah"
        Dim saveas    = "dump.xls"

        ' Path cant have \ for ending, if so, remove it
        If path.EndsWith("\") Then path = path.Remove(path.Length - 1, 1)


        ' Initialize the connections and connect to the project
        QCConnection.InitConnectionEx(QCURL)
        QCConnection.ConnectProjectEx(QCDomain, QCProject, QCUser, QCPasswd)

        If QCConnection Is Nothing Or Not QCConnection.Connected Then
            System.Console.WriteLine("nothing")
            Exit Sub
        End If

        Dim excel, sheet
        excel = CreateObject("Excel.Application")
        excel.WorkBooks.Add() 'Add a new workbook
        'Get the first worksheet.
        sheet = excel.ActiveSheet
        sheet.Name = "Tests"
        With sheet.Range("A1:H1")
            .Font.Name = "Arial"
            .Font.FontStyle = "Bold"
            .Font.Size = 10
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .Interior.ColorIndex = 15 'Light Grey
        End With

        ' setup the excel titles
        sheet.Cells(1, 1) = "Subject (Folder Name)"
        sheet.Cells(1, 2) = "Test Name (Manual Test Plan Name)"
        sheet.Cells(1, 3) = "Description"
        sheet.Cells(1, 4) = "Designer (Owner)"
        sheet.Cells(1, 5) = "Status"
        sheet.Cells(1, 6) = "Step Name"
        sheet.Cells(1, 7) = "Step Description(Action)"
        sheet.Cells(1, 8) = "Expected Result"

        Dim Row As Integer = 2

        ' grab the tests w/ filter for Subject
        Dim factory As TDAPIOLELib.TestFactory = QCConnection.TestFactory
        Dim filter As TDAPIOLELib.TDFilter = factory.Filter
        filter.Filter("TS_SUBJECT") = "^" + path + "^"
        Dim tests As TDAPIOLELib.List = factory.NewList(filter.Text)

        Dim total As Integer = tests.Count

        ' loop the retrieved tests  
        For Each test As TDAPIOLELib.Test In tests
            sheet.Cells(Row, 1).Value = test.Field("TS_SUBJECT").Path
            sheet.Cells(Row, 2).Value = test.Field("TS_NAME")
            sheet.Cells(Row, 3).Value = test.Field("TS_DESCRIPTION")
            sheet.Cells(Row, 4).Value = test.Field("TS_RESPONSIBLE")
            sheet.Cells(Row, 5).Value = test.Field("TS_STATUS")

            Dim stepFactory As TDAPIOLELib.DesignStepFactory = test.DesignStepFactory
            Dim steps = stepFactory.NewList("")

            If steps.Count <> 0 Then
                For Each [step] As TDAPIOLELib.DesignStep In steps
                    sheet.Cells(Row, 6).Value = [step].StepName
                    sheet.Cells(Row, 7).Value = [step].StepDescription
                    sheet.Cells(Row, 8).Value = [step].StepExpectedResult
                    Row = Row + 1
                Next
            Else
                Row = Row + 1
            End If

            System.Console.WriteLine("{0}/{1} has been extracted.", Row, total)

            System.Diagnostics.Debug.Print(Row)
        Next

        excel.Columns.AutoFit()
        'Save the newly created workbook and close Excel.
        excel.ActiveWorkbook.SaveAs(saveas)
        excel.Quit()

        excel = Nothing

        ' disconnect the project and release the connection object
        QCConnection.DisconnectProject()
        QCConnection = Nothing

        System.Console.WriteLine("done")

    End Sub
End Module